package yongxin.controller.kingdeeReport;

import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.converter.ExcelToFoUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import org.jeecgframework.core.common.controller.BaseController;
import org.jeecgframework.core.common.model.json.AjaxJson;
import org.jeecgframework.core.common.model.json.DataGrid;
import org.jeecgframework.core.util.StringUtil;

import org.jeecgframework.tag.core.easyui.TagUtil;
import org.jeecgframework.tag.vo.datatable.SortDirection;
import org.jeecgframework.web.system.pojo.base.TSUser;
import org.jeecgframework.web.system.service.SystemService;

import yongxin.entity.annealer.AnnealerEntity;
import yongxin.entity.check.ContractEntity;
import yongxin.entity.check.ContractProcutEntity;
import yongxin.entity.processreport.ProcessReportEntity;
import yongxin.entity.temp_record.TempRecordEntity;
import yongxin.utils.ExcelUtils;

/**
 * @Title: Controller
 * @Description: 销售合同查询
 * @author zhangdaihao
 * @date 2015-02-05 14:03:13
 * @version V1.0
 * 
 */
@Controller
@RequestMapping("/contractController")
public class ContractController extends BaseController
{
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(ContractController.class);

	@Autowired
	private SystemService systemService;

	private String message;

	public String getMessage()
	{
		return message;
	}

	public void setMessage(String message)
	{
		this.message = message;
	}

	/**
	 * 
	 * 销售合同 列表
	 * 
	 * @return
	 */
	@RequestMapping(params = "contractList")
	public ModelAndView contractList(HttpServletRequest request)
	{

		return new ModelAndView("yongxin/kingdeeReport/contractList");

	}

	/**
	 * easyui 销售合同 查询
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */

	@RequestMapping(params = "datagrid")
	public void datagrid(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid)
	{

		String sql = " SELECT v1.FDate,t2.FName AS FCustomerName,v1.FContractNo, ";
		sql += " v1.FCheckDate,v1.FTotalAmountFor,t1.FName AS FEmployeeName, ";
		sql += " t3.FName AS FItemName,t3.FNumber AS FItemNumber, ";
		sql += " t3.FModel AS FModel,u1.FText6 AS FWidth,t5.FName AS FUnitName, ";
		sql += " u1.FFactPriceFor,u1.FQuantity AS FQty,u1.FAmountIncludeTaxFor AS FAmountIncludeTaxFor, ";
		sql += " t4.FName AS FBase4_DSPName,u1.FDate1 ";
		sql += " FROM  RemoteServer.AIS2009.dbo.t_RPContract v1 ";
		sql += " INNER JOIN RemoteServer.AIS2009.dbo.t_rpContractEntry u1 ON v1.FContractID = u1.FContractID ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Emp t1 ON v1.FEmployee= t1.FItemID AND t1.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Organization t2 ON v1.FBase6 = t2.FItemID AND t2.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_ICItem t3  ON u1.FProductID=t3.FItemID AND t3.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_SubMessage t4  ON u1.FBase4=t4.FInterID AND t4.FInterID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Measureunit t5 ON u1.FUnitID=t5.FItemID AND t5.FItemID<>0 ";
		sql += "  WHERE  1 = 1 ";
		sql += " AND v1.Fstatus<2 AND isnull(v1.FChecker,0)>0 AND v1.FClassTypeID=1000019 ";
		// sql +=
		// "  AND ( t_RPContract.FDate BETWEEN '2014-08-01' AND '2014-08-31 23:59:59') ";

		String Fdate_begin = request.getParameter("FDate_begin");
		String Fdate_end = request.getParameter("FDate_end");
		String FContractNo = request.getParameter("FContractNo");
		String FCustomerName = request.getParameter("FCustomerName");
		String FItemNumber = request.getParameter("FItemNumber");
		String FItemName = request.getParameter("FItemName");
		String FModel = request.getParameter("FModel");
		String FWidth = request.getParameter("FWidth");
		String FEmployeeName = request.getParameter("FEmployeeName");

		if (StringUtil.isNotEmpty(Fdate_begin) && StringUtil.isNotEmpty(Fdate_end))
		{
			
			sql += " and v1.Fdate between " + "'" + Fdate_begin + "'" + " and " + "'" + Fdate_end + "'";
		}

		if (StringUtil.isNotEmpty(FContractNo))
		{
			
			sql += " and v1.FContractNo like '%" + FContractNo + "%'";
		}
		if (StringUtil.isNotEmpty(FCustomerName))
		{
			
			sql += " and t2.FName like '%" + FCustomerName + "%'";
		}
		if (StringUtil.isNotEmpty(FItemNumber))
		{
			
			sql += " and t3.FNumber like " + "'%" + FItemNumber + "%'";
		}
		if (StringUtil.isNotEmpty(FItemName))
		{
			
			sql += " and t3.FName like " + "'%" + FItemName + "%'";
		}
		if (StringUtil.isNotEmpty(FModel))
		{
			
			sql += " and t3.FModel = " + "'" + FModel + "'";
		}

		if (StringUtil.isNotEmpty(FWidth))
		{
			
			sql += " and u1.FText6  = " + "'" + FWidth + "'";
		}
		if (StringUtil.isNotEmpty(FEmployeeName))
		{
			
			sql += " and t1.FName  = " + "'" + FEmployeeName + "'";
		}

		if(dataGrid.getSort() != null)
		{
			String orderField = dataGrid.getSort();
			if(dataGrid.getSort().equals(orderField)){

				sql += " order by " + orderField + dataGrid.getOrder();

			}
		}
        
		
		List<Map<String, Object>> maplist = null;
		List<Map<String, Object>> allmaplist = null;

		try
		{

			allmaplist = systemService.findForJdbc(sql);
			maplist = systemService.findForJdbcParam(sql, dataGrid.getPage(), dataGrid.getRows());

		} catch (Exception ex)
		{

		}
		dataGrid.setTotal(allmaplist.size());
		dataGrid.setResults(maplist);
		// 数量和金额合计
		Double sum_qty = 0.00;
		BigDecimal amount = new BigDecimal("0.0000");
		for (Map<String, Object> map : maplist)
		{
		   sum_qty += ((Double) map.get("FQty"));
		   amount = amount.add((BigDecimal) map.get("FAmountIncludeTaxFor"));
		}
		BigDecimal   b   =   new   BigDecimal(sum_qty);  
		double   f1   =   b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue(); 
		
		dataGrid.setFooter("FUnitName:合计," + "FQty:" + f1 + ",FAmountIncludeTaxFor:" + amount);
		TagUtil.datagrid(response, dataGrid);
	}
	
	
	
	/**
	 * 
	 * 導出
	 * @param request
	 * @param response
	 * @param dataGrid
	 */
	@RequestMapping(params = "export")
	public void export(HttpServletRequest request,HttpServletResponse response, DataGrid dataGrid) {
		
		String sql = " SELECT Convert(varchar(50),v1.Fdate,23) FDate,t2.FName AS FCustomerName,v1.FContractNo, ";
		sql += " v1.FCheckDate,v1.FTotalAmountFor,t1.FName AS FEmployeeName, ";
		sql += " t3.FName AS FItemName,t3.FNumber AS FItemNumber, ";
		sql += " t3.FModel AS FModel,u1.FText6 AS FWidth,t5.FName AS FUnitName, ";
		sql += " u1.FFactPriceFor,u1.FQuantity AS FQty,u1.FAmountIncludeTaxFor AS FAmountIncludeTaxFor, ";
		sql += " t4.FName AS FBase4_DSPName,Convert(varchar(50),u1.FDate1,23) FDate1 ";
		sql += " FROM  RemoteServer.AIS2009.dbo.t_RPContract v1 ";
		sql += " INNER JOIN RemoteServer.AIS2009.dbo.t_rpContractEntry u1 ON v1.FContractID = u1.FContractID ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Emp t1 ON v1.FEmployee= t1.FItemID AND t1.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Organization t2 ON v1.FBase6 = t2.FItemID AND t2.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_ICItem t3  ON u1.FProductID=t3.FItemID AND t3.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_SubMessage t4  ON u1.FBase4=t4.FInterID AND t4.FInterID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Measureunit t5 ON u1.FUnitID=t5.FItemID AND t5.FItemID<>0 ";
		sql += "  WHERE  1 = 1 ";
		sql += " AND v1.Fstatus<2 AND isnull(v1.FChecker,0)>0 AND v1.FClassTypeID=1000019 ";
		// sql +=
		// "  AND ( t_RPContract.FDate BETWEEN '2014-08-01' AND '2014-08-31 23:59:59') ";

		String Fdate_begin = request.getParameter("FDate_begin");
		String Fdate_end = request.getParameter("FDate_end");
		String FContractNo = request.getParameter("FContractNo");
		String FCustomerName = request.getParameter("FCustomerName");
		String FItemNumber = request.getParameter("FItemNumber");
		String FItemName = request.getParameter("FItemName");
		String FModel = request.getParameter("FModel");
		String FWidth = request.getParameter("FWidth");
		String FEmployeeName = request.getParameter("FEmployeeName");

		if (StringUtil.isNotEmpty(Fdate_begin) && StringUtil.isNotEmpty(Fdate_end))
		{
			
			sql += " and v1.Fdate between " + "'" + Fdate_begin + "'" + " and " + "'" + Fdate_end + "'";
		}

		if (StringUtil.isNotEmpty(FContractNo))
		{
			
			sql += " and v1.FContractNo like '%" + FContractNo + "%'";
		}
		if (StringUtil.isNotEmpty(FCustomerName))
		{
			
			sql += " and t2.FName like '%" + FCustomerName + "%'";
		}
		if (StringUtil.isNotEmpty(FItemNumber))
		{
			
			sql += " and t3.FNumber like " + "'%" + FItemNumber + "%'";
		}
		if (StringUtil.isNotEmpty(FItemName))
		{
			
			sql += " and t3.FName like " + "'%" + FItemName + "%'";
		}
		if (StringUtil.isNotEmpty(FModel))
		{
			
			sql += " and t3.FModel = " + "'" + FModel + "'";
		}

		if (StringUtil.isNotEmpty(FWidth))
		{
			
			sql += " and u1.FText6  = " + "'" + FWidth + "'";
		}
		if (StringUtil.isNotEmpty(FEmployeeName))
		{
			
			sql += " and t1.FName  = " + "'" + FEmployeeName + "'";
		}

		if(dataGrid.getSort() != null)
		{
			String orderField = dataGrid.getSort();
			if(dataGrid.getSort().equals(orderField)){
				sql += " order by " + orderField + dataGrid.getOrder();
			}
		}
      
		List<Map<String, Object>> maplist = null;

		try
		{
			maplist = systemService.findForJdbc(sql);
			List<String[]> list = new ArrayList<String[]>();
			
			for (Map<String, Object> map : maplist) {
				String[] arr = new String[map.size()];
				arr[0] = (String) map.get("Fdate");
				arr[1] = (String) map.get("FContractNo");
				arr[2] = (String) map.get("FCustomerName");
				arr[3] = String.valueOf((BigDecimal) map.get("FTotalAmountFor"));
				arr[4] = (String) map.get("FItemNumber");
				arr[5] = (String) map.get("FItemName");
				arr[6] = (String) map.get("FBase4_DSPName");
				arr[7] = (String) map.get("FModel");
				arr[8] = (String) map.get("FWidth");
				arr[9] = (String) map.get("FUnitName");
				arr[10] = String.valueOf((Double) map.get("FQty"));
				arr[11] = String.valueOf((Double) map.get("FFactPriceFor"));
				arr[12] = String.valueOf((BigDecimal) map.get("FAmountIncludeTaxFor"));
				arr[13] = (String) map.get("FEmployeeName");
				arr[14] = (String) map.get("FDate1");
				list.add(arr);
				 
			}
		
	
			String[] columnNames = new String[] { "签订日期", "合同编号", "客户名称", "总金额","物料代码","物料名称","交货状态","厚度","宽度","单位","数量","单价","金额","业务员","交货日期"};
	       String titleName = "销售合同.xls";

			OutputStream fOut = response.getOutputStream();
			response.setHeader("Content-Disposition", "attachment; filename="
					+ new String(titleName.getBytes("GB2312"), "ISO8859-1"));

			ExcelUtils.create(request, list, columnNames, titleName, fOut);

		} catch (Exception ex)
		{

		}
		
	}

}
